package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;

import bean.Adress;
import bean.Employee;


/**
 * Handles the database access for the employee data
 * @author Martin Schophaus
 *
 */
public class DB_Employee extends Handler implements HandlerInterface<Employee> {

	public DB_Employee(Connection conn) {
		super(conn);
	}
	
	@Override
	public Employee getById(int id) throws SQLException {
		PreparedStatement stmt;
		ResultSet result;
		Employee employee;
		Adress adress = null;
		Calendar birthday;
		
		String sql =
				"SELECT x.employee_nr employee_nr, x.name name, x.fname fname, x.birthday birthday, " +
				"  y.id adress_id ,y.city city, y.street street, y.postal_cd postal_cd, y.tel tel, y.tel_mobile tel_mobile, y.tel_fax tel_fax," +
				"  z.id department_id, z.name department_name," +
				"  a.id payment_lvl_id, a.name payment_lvl" +
				" FROM employee" +
				"  JOIN adress ON (x.adress = y.id)" +
				"  JOIN department ON (x.department = z.id)" +
				"  JOIN payment_level ON (x.payment_level = a.id)" +
				" WHERE x.id = ?";
		stmt = this.getDb().prepareStatement(sql);
		stmt.setInt(1, id);
		result = stmt.executeQuery();
		
		if(result.next()){
			adress = new Adress();
			adress.setId(result.getInt("adress_id"));
			adress.setCity(result.getString("city"));
			adress.setStreet(result.getString("street"));
			adress.setPostal_cd(result.getString("postal_cd"));
			adress.setTel(result.getString("tel"));
			adress.setTel_mobile(result.getString("tel_fax"));
			adress.setEmail(result.getString("email"));
			
			birthday = Calendar.getInstance();
			birthday.setTime(result.getDate("birthday"));
						
			employee = new Employee(
					result.getString("name"),
					result.getString("fname"),
					adress,
					birthday,
					result.getInt("employee_nr"),
					result.getInt("department__id"),
					result.getInt("payment_lvl_id")
					);
			stmt.close();
			return employee;		
		}
		return null;
	}

	@Override
	public ArrayList<Employee> getAll() throws SQLException {
		PreparedStatement stmt;
		ResultSet result;
		Employee employee;
		Adress adress = null;
		Calendar birthday;
		ArrayList<Employee> employees = new ArrayList<Employee>();
		
		String sql =
				"SELECT x.employee_nr employee_nr, x.name name, x.fname fname, x.birthday birthday, " +
				"  y.id adress_id ,y.city city, y.street street, y.postal_cd postal_cd, y.tel tel, y.tel_mobile tel_mobile, y.tel_fax tel_fax," +
				"  z.id department_id, z.name department_name," +
				"  a.id payment_lvl_id, a.name payment_lvl" +
				" FROM employee" +
				"  JOIN adress ON (x.adress = y.id)" +
				"  JOIN department ON (x.department = z.id)" +
				"  JOIN payment_level ON (x.payment_level = a.id)";
		stmt = this.getDb().prepareStatement(sql);

		result = stmt.executeQuery();
		
		while(result.next()){
			adress = new Adress();
			adress.setId(result.getInt("adress_id"));
			adress.setCity(result.getString("city"));
			adress.setStreet(result.getString("street"));
			adress.setPostal_cd(result.getString("postal_cd"));
			adress.setTel(result.getString("tel"));
			adress.setTel_mobile(result.getString("tel_fax"));
			adress.setEmail(result.getString("email"));
			
			birthday = Calendar.getInstance();
			birthday.setTime(result.getDate("birthday"));
						
			employee = new Employee(
					result.getString("name"),
					result.getString("fname"),
					adress,
					birthday,
					result.getInt("employee_nr"),
					result.getInt("department_id"),
					result.getInt("payment_lvl_id")
					);
			stmt.close();
			employees.add(employee);
		}
		return employees;
	}

	@Override
	public Employee add(Employee object) throws SQLException {
		Employee employee = object;
		ResultSet result;
		int adress;
		String adress_sql = "INSERT INTO adress (city, street, postal_cd, tel, tel_mobile, tel_fax, email)" +
				" VALUES (?,?,?,?,?,?,?)";
		String sql = "INSERT INTO employee (name, fname, adress, birthday, department, payment_level)" +
				"VALUES (?,?,?,?,?,?,?)";
		
		PreparedStatement stmt = this.getDb().prepareStatement(adress_sql);
		stmt.setString(1, employee.getAdress().getCity());
		stmt.setString(2, employee.getAdress().getStreet());
		stmt.setString(3, employee.getAdress().getPostal_cd());
		stmt.setString(4, employee.getAdress().getTel());
		stmt.setString(5, employee.getAdress().getTel_mobile());
		stmt.setString(6, employee.getAdress().getTel_fax());
		stmt.setString(7, employee.getAdress().getEmail());
		stmt.execute();
		result = stmt.getGeneratedKeys();
		if(result.next()) {
			adress = result.getInt("id");
		} else {
			stmt.close();
			return null;
		}
		
		stmt = this.getDb().prepareStatement(sql);
		stmt.setString(1, employee.getName());
		stmt.setString(2, employee.getFname());
		stmt.setInt(4, adress);
		java.sql.Date date = new java.sql.Date(employee.getBirthday().getTimeInMillis());
		stmt.setDate(3, date);
		stmt.setInt(5, employee.getDepartment());
		stmt.setInt(6, employee.getPayment_level());
		stmt.execute();
		result = stmt.getGeneratedKeys();
		if(result.next()){
			employee.setEmployee_nr(result.getInt(1));
			employee.getAdress().setId(adress);
			stmt.close();
			return employee;
		} else {
			return null;
		}
	}

	@Override
	public boolean update(Employee object) throws SQLException {
		Employee employee = object;
		ResultSet result;
		int adress;
		String adress_sql = "INSERT INTO adress (city, street, postal_cd, tel, tel_mobile, tel_fax, email)" +
				" VALUES (?,?,?,?,?,?,?)";
		String sql = "UPDATE employee SET name = ?, fname = ?, adress = ?, birthday = ?, department = ?, payment_lvl = ? WHERE employee_nr = ?";
		PreparedStatement stmt = this.getDb().prepareStatement(adress_sql);
		stmt.setString(1, employee.getAdress().getCity());
		stmt.setString(2, employee.getAdress().getStreet());
		stmt.setString(3, employee.getAdress().getPostal_cd());
		stmt.setString(4, employee.getAdress().getTel());
		stmt.setString(5, employee.getAdress().getTel_mobile());
		stmt.setString(6, employee.getAdress().getTel_fax());
		stmt.setString(7, employee.getAdress().getEmail());
		
		stmt.execute();
		result = stmt.getGeneratedKeys();
		if(result.next()){
			adress = result.getInt(1);
		} else {
			stmt.close();
			return false;
		}
		
		stmt = this.getDb().prepareStatement(sql);
		stmt.setString(1, employee.getName());
		stmt.setString(2, employee.getFname());
		stmt.setInt(3, adress);
		java.sql.Date date = new java.sql.Date(employee.getBirthday().getTimeInMillis());
		stmt.setDate(4, date);
		stmt.setInt(5, employee.getDepartment());
		stmt.setInt(6, employee.getPayment_level());
		int rows = stmt.executeUpdate();
		
		stmt.close();
		
		return rows != 0 ? true:false;
	}
}